﻿using Devil;
using Excel;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text.RegularExpressions;
using TableCore;

namespace TableTranslate
{
    internal class ImportTranslateTexts : ICommand
    {
        Regex excelFileRefex = new Regex(@".+\.xls(x)?$", RegexOptions.IgnoreCase);

        void GetAllTexts(DataSet datas, Dictionary<string, string> txts)
        {
            for (int i = 0; i < datas.Tables.Count; i++)
            {
                var table = datas.Tables[i];
                if (table.Rows.Count == 0)
                    continue;
                int idCol = -1, txtCol = -1;
                var rows = table.Rows.Count;
                var cols = table.Columns.Count;
                if (rows == 0 || cols < 1)
                    continue;
                var row = rows > 0 ? table.Rows[0] : null;
                for (int c = 1; c < cols; c++)
                {
                    var txt = Utils.GetString(row[c]);
                    if (StringUtil.EqualIgnoreCase(txt, "id"))
                        idCol = c;
                    else if (StringUtil.EqualIgnoreCase(txt, "text"))
                        txtCol = c;
                    if (idCol != -1 && txtCol != -1)
                        break;
                }
                if (idCol == -1 || txtCol == -1)
                    continue;
                for (int r = 3; r < rows; r++)
                {
                    row = table.Rows[r];
                    var key = Utils.GetString(row[idCol]);
                    if (!string.IsNullOrEmpty(key))
                        txts[key] = Utils.GetString(row[txtCol]);
                }
            }
            //int idCol = -1, txtCol = -1;

            //for (int i = 1; i < datas.co)
        }

        void GetAllTexts(string workFolder, List<string> txtFiles, Dictionary<string, string> txts)
        {
            if (!Directory.Exists(workFolder))
                return;
            var stack = new Stack<DirectoryInfo>();
            stack.Push(new DirectoryInfo(workFolder));
            while (stack.Count > 0)
            {
                var dic = stack.Pop();
                if (StringUtil.EqualIgnoreCase("en", dic.Name))
                {
                    foreach (var file in dic.GetFiles())
                    {
                        try
                        {
                            DataSet datas = null;
                            using (var stream = new FileStream(file.FullName, FileMode.Open))
                            {
                                IExcelDataReader reader;
                                var ext = file.Extension;
                                if (StringUtil.EqualIgnoreCase(ext, ".xls"))
                                    reader = ExcelReaderFactory.CreateBinaryReader(stream);
                                else if (StringUtil.EqualIgnoreCase(ext, ".xlsx"))
                                    reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                                else
                                    reader = null;
                                if (reader == null)
                                    continue;
                                reader.IsFirstRowAsColumnNames = false;
                                while (reader.Read()) { }
                                datas = reader.AsDataSet();
                                stream.Close();
                            }
                            if (datas == null)
                                continue;
                            GetAllTexts(datas, txts);
                        }
                        catch (Exception e)
                        {
                            Console.WriteLine(e);
                        }
                    }
                }
                else
                {
                    if (StringUtil.EqualIgnoreCase("texts", dic.Name))
                    {
                        foreach(var f in dic.GetFiles())
                        {
                            if (excelFileRefex.IsMatch(f.FullName))
                                txtFiles.Add(f.FullName);
                        }
                        //var file = Path.Combine(dic.FullName, "Texts.xlsx");
                        //if (File.Exists(file))
                        //    txtFiles.Add(file);
                    }
                    foreach (var sub in dic.GetDirectories())
                    {
                        stack.Push(sub);
                    }
                }
            }
        }
        void ImportTranslateTxts(string txtfile, Dictionary<string, string> txts)
        {
            try
            {
                var excel = new ExcelPackage(new FileInfo(txtfile));
                var sheet = excel.Workbook.Worksheets[1];
                int cnCol = -1, enCol = -1, empty = -1;
                List<int> clearCols = new List<int>();
                for (int i = 3; i < 8; i++)
                {
                    var str = sheet.Cells[1, i].Text;
                    if (StringUtil.EqualIgnoreCase(str, "text.cn"))
                        cnCol = i;
                    else if (StringUtil.EqualIgnoreCase(str, "text.en"))
                        enCol = i;
                    else if (string.IsNullOrEmpty(str))
                    {
                        clearCols.Add(i);
                        if (empty == -1)
                            empty = i;
                    }
                }
                if (cnCol != -1 && (empty != -1 || enCol != -1))
                {
                    if (enCol == -1)
                    {
                        enCol = empty;
                        sheet.Cells[1, enCol].Value = "text.en";
                    }
                    sheet.Cells[2, enCol].Value = "string";
                    sheet.Cells[3, enCol].Value = "text.en";
                    var dirty = false;
                    var rows = sheet.Cells.Rows;
                    for (int r = 4; r <= rows; r++)
                    {
                        var idcol = sheet.Cells[r, 2].Text;
                        if (string.IsNullOrEmpty(idcol))
                            break;
                        var str = sheet.Cells[r, cnCol].Text;
                        if (string.IsNullOrEmpty(str))
                            continue;
                        string value = sheet.Cells[r, enCol].Text;
                        if (string.IsNullOrEmpty(value) && txts.TryGetValue(str, out value))
                        {
                            dirty = true;
                            sheet.Cells[r, enCol].Value = value;
                        }
                    }
                    for (int i = clearCols.Count - 1; i >= 0; i--)
                    {
                        var c = clearCols[i];
                        if (c != cnCol && c != enCol)
                        {
                            dirty = true;
                            sheet.DeleteColumn(c);
                        }
                    }
                    if (dirty)
                        excel.Save();
                }
                excel.Dispose();
                Console.WriteLine(txtfile + " Updated.");
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
            }

        }
        public void Execute()
        {
            string file;
            var workFolder = Program.ReadText("输入搜索目录或文件: ");
            if(File.Exists(workFolder))
            {
                file = workFolder;
                workFolder = Utils.GetFolderName(workFolder);
            }
            else if (Directory.Exists(workFolder))
            {
                file = null;
            }
            else
            {
                Console.WriteLine(string.Format("无效的文件或文件夹： {0}.", workFolder));
                return;
            }
            Console.WriteLine(string.Format("正在查找 {0} 中的翻译文本", workFolder));
            Dictionary<string, string> txts = new Dictionary<string, string>();
            List<string> files = new List<string>();
            GetAllTexts(workFolder, files, txts);
            if(!string.IsNullOrEmpty(file))
            {
                files.Clear();
                files.Add(file);
            }
            for (int i = 0; i < files.Count; i++)
            {
                ImportTranslateTxts(files[i], txts);
            }
        }

        public override string ToString()
        {
            return "导入翻译文本";
        }
    }
}
